package shuiguo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import shuiguo.bean.ShuiGuoBean;
import util.JDBCUtils;

public class ShuiGuoDao {
	Connection con=null;
	PreparedStatement  ps=null;
	
	public int update(ShuiGuoBean shuiguo) {
		String sql="update s_shuiguo set number=?,jinhuonum=?,jinhuomoney=? where"
				+" shuiguoname=?";
		try {
			con=JDBCUtils.getConnection();
			ps=con.prepareStatement(sql);
			ps.setInt(1, shuiguo.getNumber());
			ps.setInt(2, shuiguo.getJinHuoNum());
			ps.setBigDecimal(3, shuiguo.getJinHuoMoney());
			ps.setString(4, shuiguo.getShuiGuoName());
			//用于测试把水果名漏了
//			System.out.println(shuiguo.getNumber());
//			System.out.println(shuiguo.getJinHuoMoney());
//			System.out.println(shuiguo.getShuiGuoName());
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	public int insert(ShuiGuoBean shuiguo) {
		String sql="insert into s_shuiguo(shuiGuoName,danJia,number"
				+",jinhuonum,jinhuomoney) values(?,?,?,?,?)";
		try {
			con=JDBCUtils.getConnection();
			ps=con.prepareStatement(sql);
			ps.setString(1, shuiguo.getShuiGuoName());
			ps.setBigDecimal(2, shuiguo.getDanJia());
			ps.setInt(3, shuiguo.getNumber());
			ps.setInt(4, shuiguo.getJinHuoNum());
			ps.setBigDecimal(5, shuiguo.getJinHuoMoney());
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	public int insertLei(ShuiGuoBean shui) {
		String sql="insert into s_shuiguo(shuiguoname)values(?)";
		try {
			con=JDBCUtils.getConnection();
			ps=con.prepareStatement(sql);
			ps.setString(1, shui.getShuiGuoName());
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	public ShuiGuoBean select(ShuiGuoBean shuiguo) {
		String sql="select * from s_shuiguo where shuiguoname=?";
		ResultSet rs=null;
		try {
			con=JDBCUtils.getConnection();
			ps=con.prepareStatement(sql);
			ps.setString(1, shuiguo.getShuiGuoName());
			rs=ps.executeQuery();
			while(rs.next()) {
			ShuiGuoBean shuiguo1=new ShuiGuoBean();
			shuiguo1.setDanJia(rs.getBigDecimal("danjia"));
			shuiguo1.setJinHuoMoney(rs.getBigDecimal("jinhuomoney"));
			shuiguo1.setJinHuoNum(rs.getInt("jinhuonum"));
			shuiguo1.setNumber(rs.getInt("number"));
			shuiguo1.setShuiGuoName(rs.getString("shuiguoname"));
			shuiguo1.setXiaoShouMoney(rs.getBigDecimal("xiaoshoumoney"));
			shuiguo1.setXiaoShouNum(rs.getInt("xiaoshounum"));
			return shuiguo1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	public int updateXiao(ShuiGuoBean shui) {
		String sql="update s_shuiguo set number=?,xiaoshounum=?,xiaoshoumoney=? where "+
	"shuiguoname=?";
		try {
			con=JDBCUtils.getConnection();
			ps=con.prepareStatement(sql);
			ps.setInt(1, shui.getNumber());
			ps.setInt(2, shui.getXiaoShouNum());
			ps.setBigDecimal(3, shui.getXiaoShouMoney());
			ps.setString(4, shui.getShuiGuoName());
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	public List<ShuiGuoBean> selectAll(){
		String sql="select * from s_shuiguo";
		ResultSet rs=null;
		List<ShuiGuoBean> list=new ArrayList<>();
		try {
			con=JDBCUtils.getConnection();
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				ShuiGuoBean shui=new ShuiGuoBean();
				shui.setDanJia(rs.getBigDecimal("danjia"));
				shui.setJinHuoMoney(rs.getBigDecimal("jinhuomoney"));
				shui.setJinHuoNum(rs.getInt("jinhuonum"));
				shui.setNumber(rs.getInt("number"));
				shui.setShuiGuoName(rs.getString("shuiguoname"));
				shui.setXiaoShouMoney(rs.getBigDecimal("xiaoshoumoney"));
				shui.setXiaoShouNum(rs.getInt("xiaoshounum"));
				list.add(shui);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
}
